package com.wframe.project.former.dataservices.marketing;

import com.frame.database.JBaseDao;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.wframe.project.former.dataservices.products.dao.ProductClassDao;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.HibernateException;

import java.io.Serializable;
import java.util.*;

/**
 * Created by Administrator on 2017/6/14.
 */
public class FullGiftsPromotionsDao extends JBaseDao {
    public FullGiftsPromotionsDao() {
        super("wb_promotions");
    }

    public void FullGiftsUpDataStop(int id, int state) throws Exception {
        getSession().update("update wb_promotions set state=? where id=?", new Object[]{state, id});
    }

    public List<JdataBean> canCreate() throws Exception {
        return getSession().query("SELECT * from wb_promotions where (promotion_type=3 or promotion_type = 2 ) and product_type is not null and end_time > now()", null);
    }

    public List<JdataBean> canCreate(int proClass) throws Exception {
        String sql = "SELECT id FROM wb_promotions where (promotion_type = 3 or promotion_type = 2) and end_time > now() " +
                "and (product_type=0 " +
                "or (product_type=3 and (product_val=? or product_val=1 or 1 = ?)) " +
                "or (product_type=2 and product_val in(SELECT wpp.promotionid from wb_promotion_product wpp ,wb_products_and_class wac where " +
                "wac.productid = wpp.productid and wac.classid=?)) " +
                "or (product_type=1 and product_val in (SELECT productid from wb_products_and_class where classid=?))" +
                ")";
        return getSession().query(sql, new Object[]{proClass, proClass, proClass, proClass});
    }
    public List<JdataBean> canCreateTwoClassId(int proClass) throws Exception {
        String sql = "SELECT id FROM wb_promotions where (promotion_type = 3 or promotion_type = 2) and end_time > now() and (product_type=0 " +
                "or (product_type=3 and (product_val = ? or product_val in (select id from wb_products_class where pid = ?))) " +
                "or (product_type=2 and product_val in(SELECT promotionid FROM wb_promotion_product WHERE productid IN (SELECT productid FROM wb_products_and_class WHERE classid in (select id from wb_products_class where pid = ?)))) " +
                "or (product_type=1 and product_val in (SELECT productid FROM wb_products_and_class WHERE classid in (select id from wb_products_class where pid = ?))))";
        return getSession().query(sql, new Object[]{proClass, proClass, proClass, proClass});
    }
    public List<JdataBean> canCreateByClassId(int proClass) throws Exception {
        String sql = "SELECT  " +
                " id  " +
                "FROM  " +
                " wb_promotions  " +
                "WHERE  " +
                " (" +
                "  promotion_type = 3  " +
                "  OR promotion_type = 2  " +
                " )  " +
                "AND end_time > now()  " +
                "AND (" +
                " product_type = 0  " +
                " OR (" +
                "  product_type = 3  " +
                "  AND (  " +
                "   product_val IN (" +
                "    SELECT  " +
                "     pc.id  " +
                "    FROM  " +
                "     wb_products_class pc  " +
                "    RIGHT JOIN (" +
                "     SELECT  " +
                "      pd.*  " +
                "     FROM  " +
                "      wb_products_class pd  " +
                "     WHERE  " +
                "      pd.id = ?" +
                "    ) pe ON (  " +
                "     pc.id = pe.id  " +
                "     OR pe. CODE LIKE concat(pc. CODE, '.%')  " +
                "    )  " +
                "   )  " +
                "  )  " +
                " )  " +
                " OR (  " +
                "  product_type = 2  " +
                "  AND product_val IN (  " +
                "   SELECT  " +
                "    wpp.promotionid  " +
                "   FROM  " +
                "    wb_promotion_product wpp  " +
                "   WHERE  " +
                "    wpp.productid IN (  " +
                "     SELECT  " +
                "      productid  " +
                "     FROM  " +
                "      wb_products_and_class  " +
                "     WHERE  " +
                "      classid IN (  " +
                "       SELECT  " +
                "        pc.id  " +
                "       FROM  " +
                "        wb_products_class pc  " +
                "       RIGHT JOIN (  " +
                "        SELECT  " +
                "         pd.*  " +
                "        FROM  " +
                "         wb_products_class pd  " +
                "        WHERE  " +
                "         pd.id = ?" +
                "       ) pe ON (  " +
                "        pc. CODE LIKE concat(pe. CODE, '.%')  " +
                "        OR pc. CODE = pe. CODE  " +
                "       )  " +
                "      )  " +
                "    )  " +
                "  )  " +
                "  OR (  " +
                "   product_type = 1  " +
                "   AND product_val IN (  " +
                "    SELECT  " +
                "     productid  " +
                "    FROM  " +
                "     wb_products_and_class  " +
                "    WHERE  " +
                "     classid IN (  " +
                "      SELECT  " +
                "       pc.id  " +
                "      FROM  " +
                "       wb_products_class pc  " +
                "      RIGHT JOIN (  " +
                "       SELECT  " +
                "        pd.*  " +
                "       FROM  " +
                "        wb_products_class pd  " +
                "       WHERE  " +
                "        pd.id = ?" +
                "      ) pe ON (  " +
                "       pc. CODE LIKE concat(pe. CODE, '.%')  " +
                "       OR pc. CODE = pe. CODE" +
                "      )  " +
                "     )  " +
                "   )  " +
                "  )  " +
                " )  " +
                ") " ;
        return getSession().query(sql, new Object[]{proClass, proClass, proClass});
    }

    public List<JdataBean> canCreate(List<String> ids) throws Exception {
        ArrayList<Object> list = new ArrayList<>();
        for (int i = 0 ; i < 3 ; i++) {
            for (int j = 0; j < ids.size() ; j++) {
                list.add(ids.get(j));
            }

        }
        String inIds = "";
        for (int i = 0; i < ids.size(); i++) {
            if (i == ids.size() - 1) {
                inIds += "?";
                break;
            }
            inIds += "?,";
        }
        /*String sql = "SELECT id FROM wb_promotions where (promotion_type = 3 or promotion_type = 2) and end_time > now() and (state =0 or state =1)" +
                "and (product_type=0 or " +
                "(product_type=1 and product_val in (" + inIds + " ) ) " +
                "or (product_type=2 and product_val in (SELECT promotionid FROM wb_promotion_product where productid in (" + inIds + "))) " +
                "or (product_type=3 and product_val in (SELECT id FROM wb_products_and_class WHERE productid in (" + inIds + "))) " +
                ")";*/
        String sql = "SELECT " +
                " id " +
                "FROM " +
                " wb_promotions " +
                "WHERE " +
                " (" +
                " promotion_type = 3 " +
                "  OR promotion_type = 2" +
                " ) " +
                "AND end_time > now() " +
                "AND (state = 0 OR state = 1) " +
                "AND (" +
                " product_type = 0 " +
                " OR ( " +
                "  product_type = 1 " +
                "  AND product_val IN (" + inIds + ") " +
                " ) " +
                " OR ( " +
                "  product_type = 2 " +
                "  AND product_val IN ( " +
                "   SELECT " +
                "    promotionid " +
                "   FROM " +
                " wb_promotion_product " +
                " WHERE " +
                " productid IN (" + inIds + ") " +
                " )" +
                " )" +
                " OR (" +
                "  product_type = 3 " +
                "  AND product_val IN ( " +
                "  SELECT DISTINCT " +
                "  pc.id " +
                "   FROM " +
                "   wb_products_class pc " +
                "   RIGHT JOIN ( " +
                "    SELECT " +
                "     * " +
                "    FROM " +
                "     wb_products_and_class " +
                "    WHERE " +
                "     productid IN (" + inIds + ") " +
                "   ) pac ON ( " +
                "    pc.id = pac.classid " +
                "    OR pac.classcode LIKE CONCAT(pc.`code`, '.%') " +
                "   ) " +
                "  ) " +
                " ) " +
                ")";
        return getSession().query(sql, list.toArray(new String[list.size()]));
    }

    public JdataBean findOne(String id) throws Exception {
        String sql = "SELECT * from wb_promotions WHERE id=?";
        JdataBean result = getSession().getObject(sql, new Object[]{id});
        sql = "SELECT * from wb_promotion_purchase_gift WHERE promotionid=?";
        List<Map> items = getSession().queryMap(sql, new Object[]{id});
        for (Map item : items) {
            sql = "SELECT z.*,p.name name from wb_promotion_purchase_gift_product z,wb_products p  WHERE p.id = z.productid and giftid = ?";
            List<Map> gifts = getSession().queryMap(sql, new Object[]{item.get("id")});
            item.put("gifts", gifts);
        }
        if (items.get(0).get("min_number") != null) {
            //满数量赠送
            result.put("gift_type", 1);
            result.put("itemsnum", items);
            result.put("items", null);
        } else {
            //满金额赠送
            result.put("gift_type", 0);
            result.put("items", items);
            result.put("itemsnum", null);
        }

        return result;
    }

    public void delFullGifts(Object id) throws Exception {
        String sql = "DELETE wb_promotion_purchase_gift,wb_promotion_product,wb_promotion_purchase_gift_product from wb_promotion_purchase_gift"
                + " LEFT JOIN wb_promotion_product on wb_promotion_purchase_gift.promotionid=wb_promotion_product.promotionid"
                + " JOIN wb_promotion_purchase_gift_product on wb_promotion_purchase_gift.id=wb_promotion_purchase_gift_product.giftid"
                + " WHERE wb_promotion_purchase_gift.promotionid=?";
        getSession().delete(sql, new Object[]{id});
    }

    public void saveProducts(Object promotionId, List list) throws Exception {
        for (int i = 0; i < list.size(); i++) {
            Map map = new HashMap();
            map.put("promotionid", promotionId);
            map.put("productid", list.get(i));
            getSession().save("wb_promotion_product", map);
        }
    }

    public PageResult queryMoneyProduct(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "SELECT m.id,m.`name` pname,m.`code`,(select (ifnull(pr.amount,0)-ifnull(pr.djamount,0)) totalamount from  wb_products_prices pr where pr.productid=m.id)productamount from wb_products m where m.status=2 ";
        List parmlist = new ArrayList();
        String[] codelist = null;
        String code = "";
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("pname"))) {
                sql += " and m.name like ?";
                parmlist.add("%" + parms.getString("pname") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("code"))) {
                String codestr = parms.getString("code");
                sql += " and m.code in (" + codestr + ") ";
            }
            if (StringUtils.isNotBlank(parms.getString("classid"))) {
                if ("null".equals(parms.getString("classid"))) {
                    sql += " and m.id not in (select productid from wb_products_and_class)";
                } else {
                    JdataBean clsbean = new ProductClassDao().getBean(parms.getString("classid"));
                    sql += " and m.id in (select productid from wb_products_and_class where classcode like ? or classcode=?)";
                    parmlist.add(clsbean.getString("code") + ".%");
                    parmlist.add(clsbean.getString("code"));
                }
            }
        }
        sql += " group by m.id desc";
        return getSession().query2(sql, parmlist, size, pageno);
    }

    public String addControlsalesSingLe(JdataBean bean) throws Exception {
        return getSession().saveReturnKey("wb_promotion_controlsale", bean.getDataMap());
    }

    public void deleteControlsalesAll(String promotionid) throws HibernateException, Exception{
        getSession().delete("delete from wb_promotion_controlsale where promotion_id=?",new Object[]{promotionid});
    }

    public void updatPromotecontrol(String protomctid,int iscontrol) throws Exception{
        getSession().update("update wb_promotions set is_control=? where id=?",new Object[]{iscontrol,protomctid});
    }

    public List<JdataBean> chooseGifts(String giftName,int rows) throws Exception{
        String sql = null;
        Object[] objArr = null;
        if (StringUtils.isNotBlank(giftName)) {
            sql = "select p.id,p.name productName,p.code productCode from wb_products p where p.status=2 and p.code=? or p.name like ? limit ?,10";
            String name = "%" + giftName + "%";
            objArr = new Object[]{giftName,name,rows};
        }else {
            sql = "select p.id,p.name productName,p.code productCode from wb_products p where p.status=2 limit ?,10";
            objArr = new Object[]{rows};
        }
        return getSession().query(sql,objArr);
    }
}
